﻿Public Class DcliConyCoop

    'Public Shared Function Añadir(ByVal descEmpresa As String, ByVal cliConyCoop As EcliConyCoop) As Boolean
    '    Dim resultado As Boolean = False

    '    Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
    '        Using cmd As New SqlCommand("usp_cliConyCoop_add", cn)
    '            cmd.CommandType = CommandType.StoredProcedure
    '            cmd.Parameters.Add("@idProyecto", SqlDbType.Int).Value = cliConyCoop.idProyecto
    '            cmd.Parameters.Add("@idEtapa", SqlDbType.Int).Value = cliConyCoop.idEtapa
    '            cmd.Parameters.Add("@idSeparacionVenta", SqlDbType.Int).Value = cliConyCoop.idSeparacionVenta
    '            cmd.Parameters.Add("@idCliente", SqlDbType.Int).Value = cliConyCoop.idCliente
    '            cmd.Parameters.Add("@idTpoCliente", SqlDbType.Int).Value = cliConyCoop.idTpoCliente
    '            Try
    '                cn.Open()
    '                cmd.ExecuteNonQuery()
    '                resultado = True
    '            Catch ex As Exception
    '                Throw
    '            End Try
    '        End Using
    '    End Using

    '    Return resultado
    'End Function
    Public Shared Function SeleccionarClienteConyugue(ByVal descEmpresa As String, ByVal idRelacion As Int32) As List(Of EcliConyCoop)
        Dim cliConyCoops As List(Of EcliConyCoop) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_clienteConyugue_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idRelacion", SqlDbType.Int).Value = idRelacion
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            cliConyCoops = New List(Of EcliConyCoop)
                            Dim cliConyCoop As EcliConyCoop = Nothing 'tipo 
                            Do While dr.Read
                                cliConyCoop = New EcliConyCoop( _
                                    dr.GetInt32(dr.GetOrdinal("idCliente")), _
                                    dr.GetInt32(dr.GetOrdinal("idTpoDcmto")), _
                                    dr.GetString(dr.GetOrdinal("nroDocumento")), _
                                    dr.GetInt32(dr.GetOrdinal("edad")), _
                                    dr.GetString(dr.GetOrdinal("nombre")), _
                                    dr.GetString(dr.GetOrdinal("apePaterno")), _
                                    dr.GetString(dr.GetOrdinal("apeMaterno")), _
                                    dr.GetString(dr.GetOrdinal("fchNacimineto")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstCivil")), _
                                    dr.GetInt32(dr.GetOrdinal("nroDependientes")), _
                                    dr.GetInt32(dr.GetOrdinal("sexo")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstudio")), _
                                    dr.GetString(dr.GetOrdinal("nacionalidad")), _
                                    dr.GetString(dr.GetOrdinal("telefonoFijo")), _
                                    dr.GetString(dr.GetOrdinal("telefonoCelular")), _
                                    dr.GetString(dr.GetOrdinal("email")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacion")), _
                                    dr.GetString(dr.GetOrdinal("direccion")), _
                                    dr.GetInt32(dr.GetOrdinal("separacionBienes")), _
                                    dr.GetString(dr.GetOrdinal("nroPartida")), _
                                    dr.GetInt32(dr.GetOrdinal("idProfesion")), _
                                    dr.GetInt32(dr.GetOrdinal("idOcupacion")), _
                                    dr.GetString(dr.GetOrdinal("ruc")), _
                                    dr.GetString(dr.GetOrdinal("razonSocialLaboral")), _
                                    dr.GetString(dr.GetOrdinal("giro")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("direccionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral2")), _
                                    dr.GetString(dr.GetOrdinal("puesto")), _
                                    dr.GetInt32(dr.GetOrdinal("antiguedadLaboral")), _
                                    dr.GetDecimal(dr.GetOrdinal("ingresos")), _
                                    dr.GetString(dr.GetOrdinal("estado")), _
                                    dr.GetInt32(dr.GetOrdinal("idUsuario")))
                                cliConyCoops.Add(cliConyCoop)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return cliConyCoops
    End Function
    Public Shared Function SeleccionarConyugue(ByVal descEmpresa As String, ByVal idRelacion As Int32) As List(Of EcliConyCoop)
        Dim cliConyCoops As List(Of EcliConyCoop) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_clienteConyugue_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idRelacion", SqlDbType.Int).Value = idRelacion
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            cliConyCoops = New List(Of EcliConyCoop)
                            Dim cliConyCoop As EcliConyCoop = Nothing 'tipo 
                            Do While dr.Read
                                cliConyCoop = New EcliConyCoop( _
                                    dr.GetInt32(dr.GetOrdinal("idCliente")), _
                                    dr.GetInt32(dr.GetOrdinal("idTpoDcmto")), _
                                    dr.GetString(dr.GetOrdinal("nroDocumento")), _
                                    dr.GetInt32(dr.GetOrdinal("edad")), _
                                    dr.GetString(dr.GetOrdinal("nombre")), _
                                    dr.GetString(dr.GetOrdinal("apePaterno")), _
                                    dr.GetString(dr.GetOrdinal("apeMaterno")), _
                                    dr.GetString(dr.GetOrdinal("fchNacimineto")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstCivil")), _
                                    dr.GetInt32(dr.GetOrdinal("nroDependientes")), _
                                    dr.GetInt32(dr.GetOrdinal("sexo")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstudio")), _
                                    dr.GetString(dr.GetOrdinal("nacionalidad")), _
                                    dr.GetString(dr.GetOrdinal("telefonoFijo")), _
                                    dr.GetString(dr.GetOrdinal("telefonoCelular")), _
                                    dr.GetString(dr.GetOrdinal("email")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacion")), _
                                    dr.GetString(dr.GetOrdinal("direccion")), _
                                    dr.GetInt32(dr.GetOrdinal("separacionBienes")), _
                                    dr.GetString(dr.GetOrdinal("nroPartida")), _
                                    dr.GetInt32(dr.GetOrdinal("idProfesion")), _
                                    dr.GetInt32(dr.GetOrdinal("idOcupacion")), _
                                    dr.GetString(dr.GetOrdinal("ruc")), _
                                    dr.GetString(dr.GetOrdinal("razonSocialLaboral")), _
                                    dr.GetString(dr.GetOrdinal("giro")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("direccionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral2")), _
                                    dr.GetString(dr.GetOrdinal("puesto")), _
                                    dr.GetInt32(dr.GetOrdinal("antiguedadLaboral")), _
                                    dr.GetDecimal(dr.GetOrdinal("ingresos")), _
                                    dr.GetString(dr.GetOrdinal("estado")), _
                                    dr.GetInt32(dr.GetOrdinal("idUsuario")))
                                cliConyCoops.Add(cliConyCoop)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return cliConyCoops
    End Function
    Public Shared Function SeleccionarClienteConyugueTemporal(ByVal descEmpresa As String, ByVal idRelacion As Int32) As List(Of EcliConyCoop)
        Dim cliConyCoops As List(Of EcliConyCoop) = Nothing

        Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(descEmpresa).ConnectionString)
            Using cmd As New SqlCommand("usp_clienteConyugueTemp_sel", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@idRelacion", SqlDbType.Int).Value = idRelacion
                Try
                    cn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            cliConyCoops = New List(Of EcliConyCoop)
                            Dim cliConyCoop As EcliConyCoop = Nothing 'tipo 
                            Do While dr.Read
                                cliConyCoop = New EcliConyCoop( _
                                    dr.GetInt32(dr.GetOrdinal("idCliente")), _
                                    dr.GetInt32(dr.GetOrdinal("idTpoDcmto")), _
                                    dr.GetString(dr.GetOrdinal("nroDocumento")), _
                                    dr.GetInt32(dr.GetOrdinal("edad")), _
                                    dr.GetString(dr.GetOrdinal("nombre")), _
                                    dr.GetString(dr.GetOrdinal("apePaterno")), _
                                    dr.GetString(dr.GetOrdinal("apeMaterno")), _
                                    dr.GetString(dr.GetOrdinal("fchNacimineto")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstCivil")), _
                                    dr.GetInt32(dr.GetOrdinal("nroDependientes")), _
                                    dr.GetInt32(dr.GetOrdinal("sexo")), _
                                    dr.GetInt32(dr.GetOrdinal("idEstudio")), _
                                    dr.GetString(dr.GetOrdinal("nacionalidad")), _
                                    dr.GetString(dr.GetOrdinal("telefonoFijo")), _
                                    dr.GetString(dr.GetOrdinal("telefonoCelular")), _
                                    dr.GetString(dr.GetOrdinal("email")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeo")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacion")), _
                                    dr.GetString(dr.GetOrdinal("direccion")), _
                                    dr.GetInt32(dr.GetOrdinal("separacionBienes")), _
                                    dr.GetString(dr.GetOrdinal("nroPartida")), _
                                    dr.GetInt32(dr.GetOrdinal("idProfesion")), _
                                    dr.GetInt32(dr.GetOrdinal("idOcupacion")), _
                                    dr.GetString(dr.GetOrdinal("ruc")), _
                                    dr.GetString(dr.GetOrdinal("razonSocialLaboral")), _
                                    dr.GetString(dr.GetOrdinal("giro")), _
                                    dr.GetString(dr.GetOrdinal("idUbigeoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("urbanizacionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("direccionLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral")), _
                                    dr.GetString(dr.GetOrdinal("telefonoLaboral2")), _
                                    dr.GetString(dr.GetOrdinal("puesto")), _
                                    dr.GetInt32(dr.GetOrdinal("antiguedadLaboral")), _
                                    dr.GetDecimal(dr.GetOrdinal("ingresos")), _
                                    dr.GetString(dr.GetOrdinal("estado")), _
                                    dr.GetInt32(dr.GetOrdinal("idUsuario")))
                                cliConyCoops.Add(cliConyCoop)
                            Loop
                            dr.Close()
                        End If
                    End Using
                Catch ex As Exception
                    Throw
                End Try
            End Using
        End Using

        Return cliConyCoops
    End Function

End Class
